<?php
App::uses('AppModel', 'Model');
/**
 * Question Model
 *
 * @property bloc_question $bloc_question
 * @property articles_categorie $articles_categorie
 * @property reponses_donnee $reponses_donnee
 * @property action_question $action_question
 * @property reponse $reponse
 */
class Question extends AppModel {

	// permet de supprimer les règles associer à une question puis de la desactiver
	// paramètres :
	// - id : id de la question
	public function supprimerRegleQuestion($id) {
		// suppression des règles de type réponse/réponse pour les réponses inférentes de cette question
		$this->query("DELETE nouveau_statuts FROM questions ".
					 "JOIN reponses ON reponses.id_question = questions.id_question ".
					 "JOIN nouveau_statuts ON nouveau_statuts.id_reponse = reponses.id_reponse ".
					 "WHERE questions.id_question = $id");
		// suppression des règles de type réponse/réponse pour les réponses inférées de cette questions
		$this->query("DELETE nouveau_statuts FROM questions ".
					 "JOIN reponses ON reponses.id_question = questions.id_question ".
					 "JOIN nouveau_statuts ON nouveau_statuts.id_reponse_statut_change_par = reponses.id_reponse ".
					 "WHERE questions.id_question = $id");
		// suppression des règles de type question/réponse pour la question inféré
		$this->query("DELETE action_questions FROM questions ".
					 "JOIN reponses ON reponses.id_question = questions.id_question ".
					 "JOIN action_questions ON reponses.id_reponse = action_questions.id_reponse ".
					 "WHERE questions.id_question = $id");
		// suppresion des règles de type question/réponse avec la question inféré
		$this->query("DELETE action_questions FROM questions ".
					 "JOIN action_questions ON questions.id_question = action_questions.id_question ".
					 "WHERE questions.id_question = $id");
		// suppression des inférences sur les règles de type question/devis dans lesquelles la question infère sur la quantite
		$this->query("DELETE quantite_articles FROM questions ".
					 "JOIN quantite_articles ON questions.id_question = quantite_articles.id_question ".
					 "WHERE questions.id_question = $id");
		// suppression des règles de type question/devis dans lesquelles les réponses de cette questions sont calculés sur des quantités d'autres questions
		$ajout_lignes = $this->query("SELECT declenche_ajout_lignes.id_ajout_ligne FROM questions ".
									"JOIN reponses ON questions.id_question = reponses.id_question ".
									"JOIN declenche_ajout_lignes ON declenche_ajout_lignes.id_reponse = reponses.id_reponse ".
									"WHERE questions.id_question = $id");
		foreach ($ajout_lignes as $ajout_ligne) {
			$this->query("DELETE FROM quantite_articles WHERE id_ajout_ligne = ".$ajout_ligne['declenche_ajout_lignes']['id_ajout_ligne']);
			$this->query("DELETE FROM declenche_ajout_lignes WHERE id_ajout_ligne = ".$ajout_ligne['declenche_ajout_lignes']['id_ajout_ligne']);
			$this->query("DELETE FROM ajout_lignes WHERE id_ajout_ligne = ".$ajout_ligne['declenche_ajout_lignes']['id_ajout_ligne']);
		}
	}

	// permet d'activer un ensemble de questions
	// paramètres :
	// - ids : un tableau dont chaque case correspond à l'id d'une question
	public function activerQuestions($ids) {
		$first = true;
		$query = "UPDATE vox_blocs, bloc_questions, questions SET vox_blocs.active = 1, bloc_questions.active = 1, questions.active = 1 WHERE bloc_questions.id_vox_bloc = vox_blocs.id_vox_bloc AND questions.id_bloc = bloc_questions.id_bloc AND (";
		
		foreach ($ids as $id) {
			if($first) $first = false; else $query .= " OR ";
			$query .= "id_question = $id";
		}
		
		$this->query($query.")");
	}

	// récupère toutes les questions dont l'id est dans le tableau d'id passé en paramètres.
	// paramètres :
	// - ids : un array dont chaque case correspont à l'id d'une question
	// retour :
	// un tableau de contenant le format suivant : {id, libelle}
	public function obtenirQuestions($ids) {
		$first = true;
		$query = "SELECT questions.id_question, questions.libelle_question FROM questions WHERE ";
		
		foreach ($ids as $id) {
			if($first) $first = false; else $query .= " OR ";
			$query .= "id_question = $id";
		}
		
		$result = $this->query($query);
		$retour = array();
		
		foreach ($result as $ligne) {
			$retour[] = array('id' => $ligne['questions']['id_question'], 'libelle' => $ligne['questions']['libelle_question']);
		}
		
		return $retour;
	}

	public function questionsDesactiverAvecRegle() {
		// on récupère les règles de type réponse/réponse pour les réponses inférentes
		$questions_1 = $this->query("SELECT questions.id_question FROM questions ".
								  "JOIN reponses ON reponses.id_question = questions.id_question ".
								  "JOIN nouveau_statuts ON nouveau_statuts.id_reponse = reponses.id_reponse ".
								  "WHERE active = 0 AND formulaire_question = 'SITE'");
		// on récupère les règles de type réponse/réponse pour les réponses inférées
		$questions_2 = $this->query("SELECT questions.id_question FROM questions ".
								  "JOIN reponses ON reponses.id_question = questions.id_question ".
								  "JOIN nouveau_statuts ON nouveau_statuts.id_reponse_statut_change_par = reponses.id_reponse ".
								  "WHERE active = 0 AND formulaire_question = 'SITE'");
		// on récupère les règles de type question/réponse avec la réponse inférente
		$questions_3 = $this->query("SELECT questions.id_question FROM questions ".
									"JOIN reponses ON reponses.id_question = questions.id_question ".
									"JOIN action_questions ON reponses.id_reponse = action_questions.id_reponse ".
									"WHERE active = 0 AND formulaire_question = 'SITE'");
		// on récupère les règles de type question/réponse avec la question inféré
		$questions_4 = $this->query("SELECT questions.id_question FROM questions ".
									"JOIN action_questions ON questions.id_question = action_questions.id_question ".
									"WHERE active = 0 AND formulaire_question = 'SITE'");
		// on récupères les règles de type question/devis dans lesquelles les questions infèrent sur des quantités
		$questions_5 = $this->query("SELECT questions.id_question FROM questions ".
									"JOIN quantite_articles ON questions.id_question = quantite_articles.id_question ".
									"WHERE active = 0 AND formulaire_question = 'SITE'");
		// on récupères les règles de type question/devis dans lesquelles les réponses conditionne l'execution de la règle
		$questions_6 = $this->query("SELECT questions.id_question FROM questions ".
									"JOIN reponses ON questions.id_question = reponses.id_question ".
									"JOIN declenche_ajout_lignes ON declenche_ajout_lignes.id_reponse = reponses.id_reponse ".
									"WHERE questions.active = 0 AND formulaire_question = 'SITE'");
		// on ajoute tout les id à un tableau unique
		$ids = array();
		foreach ($questions_1 as $q) $ids[$q['questions']['id_question']] = true;
		foreach ($questions_2 as $q) $ids[$q['questions']['id_question']] = true;
		foreach ($questions_3 as $q) $ids[$q['questions']['id_question']] = true;
		foreach ($questions_4 as $q) $ids[$q['questions']['id_question']] = true;
		foreach ($questions_5 as $q) $ids[$q['questions']['id_question']] = true;
		foreach ($questions_6 as $q) $ids[$q['questions']['id_question']] = true;
		
		$liste_id = array();
		
		foreach ($ids as $k => $v) $liste_id[] = $k;
		
		return $liste_id;
	}

	public function supprimerRegleDesQuestionsDesactivees() {
		// supression des règles de type réponses/articles
		// questions influencées
		// $this->query("DELETE DAL FROM questions Q
					// JOIN quantite_articles QA ON QA.id_question = Q.id_question
					// JOIN ajout_lignes AJ ON AJ.id_ajout_ligne = QA.id_ajout_ligne
					// JOIN declenche_ajout_lignes DAL ON DAL.id_ajout_ligne
					// WHERE Q.active = 0");
		// $this->query("DELETE AJ FROM questions Q
					// JOIN quantite_articles QA ON QA.id_question = Q.id_question
					// JOIN ajout_lignes AJ ON AJ.id_ajout_ligne = QA.id_ajout_ligne
					// WHERE Q.active = 0");
		// $this->query("DELETE QA FROM questions Q
					// JOIN quantite_articles QA ON QA.id_question = Q.id_question
					// WHERE Q.active = 0");
		// questions influençantes
		// $this->query("DELETE ");
		
		// supression des règles de type réponses/réponses
		// $this->query("DELETE NS FROM questions Q
					// JOIN reponses R ON R.id_question = Q.id_question
					// JOIN nouveau_statuts NS ON NS.id_reponse = R.id_reponse
					// WHERE Q.active = 0");
		// $this->query("DELETE NS FROM questions Q
					// JOIN reponses R ON R.id_question = Q.id_question
					// JOIN nouveau_statuts NS ON NS.id_reponse_statut_change_par = R.id_reponse
					// WHERE Q.active = 0");
		// supression des règles de type réponses/questions
		
	}

	// retourne l'ensemble des questions sur les sociétés (n'ayant donc pas de bloc)
	public function toutesLesQuestionsSurSite() {
		$result = $this->query("SELECT * FROM questions Question ".
								"LEFT JOIN reponses Reponse ON Question.id_question = Reponse.id_question ".
								"WHERE Question.active AND Question.formulaire_question='SITE' AND id_bloc IS NULL ".
								"ORDER BY Question.rang_question, Reponse.rang_reponse");
		$out = array();
		foreach($result as $r) {
			// si la question n'existe pas déjà, on l'ajoute
			if(!isset($out[$r['Question']['rang_question']])) {
				$out[$r['Question']['id_question']] = array();
				$out[$r['Question']['id_question']] = $r['Question'];
				$out[$r['Question']['id_question']]['reponses'] = array();
			}
			// si il y à une réponse, on l'ajoute
			if(isset($r['Reponse']['id_reponse']) && $r['Reponse']['id_reponse'] != null) {
				$out[$r['Question']['id_question']]['reponses'][$r['Reponse']['id_reponse']] = $r['Reponse'];
				//$out[$r['Question']['id_question']]['reponses'][$r['Reponse']['id_reponse']]['libelle_statut_par_defaut'] = $r['SR_defaut']['libelle_statut_reponse'];
				$out[$r['Question']['id_question']]['reponses'][$r['Reponse']['id_reponse']]['actions'] = Array();
				$out[$r['Question']['id_question']]['reponses'][$r['Reponse']['id_reponse']]['change_statut'] = Array();
			}
		}
		return $out;
	}

	// ne conserve que les réponses voulues pour une question
	// paramètres :
	// id_question : l'id de la question concernée
	// ids_reponse : un tableau contenant les ids de questions
	function conserverReponses($id_question, $ids_reponse) {
		if(count($ids_reponse) > 0) {
			$requete = 'DELETE FROM reponses WHERE id_question = '.$id_question;
			$first = true;
			foreach ($ids_reponse as $id_reponse) {
				$requete .= ' AND id_reponse <> '.$id_reponse;
			}
			$this->query($requete);
		}
	}

	public function desactiverToutesLesQuestions() {
		$this->query("UPDATE questions SET active=0");
	}
	
	// Pour remplir les combos des règles
	// Tableau avec les questions actives et leur réponses, triées par bloc, ensemble et question
	public function questionsEtReponses(){
		$result = $this->query("SELECT * ".
								"FROM questions q ".
								"LEFT JOIN reponses r ON q.id_question = r.id_question ".
								"LEFT JOIN nouveau_statuts ns ON ns.id_reponse = r.id_reponse ". 
								"JOIN bloc_questions bq ON bq.id_bloc = q.id_bloc ".
								"JOIN vox_blocs vb ON vb.id_vox_bloc = bq.id_vox_bloc ".
								"WHERE q.active ".
								"ORDER BY vb.rang_vox_bloc, bq.rang_bloc, q.rang_question"
				);
		$out = array();
		
		foreach ($result as $r) {
			$key = $r['vb']['rang_vox_bloc'].'_'.$r['bq']['rang_bloc'].'_'.$r['q']['rang_question'];
			// si cette question n'a pas d�j� �t� crois�, on l'ajout a la liste des question
			if(!isset($out[$key])) {
				$out[$key] = Array();
				$out[$key]['info'] = $r['q'];
			}
			// on ajoute la r�ponse � la liste
			if($r['r']['id_reponse'] != null) {
				$out[$key]['r'][] = $r['r'];
			}
		}
		
		return $out;
		
		
	}
	
	// renvoie un tableau des questions qui vont dans la page finalisation (formulaire_question="DEVIS") avec leurs réponses 
	public function questionsEtReponsesFinalisation() {
		$result = $this->query("SELECT * ".
								"FROM questions q ".
								"LEFT JOIN reponses r ON q.id_question = r.id_question ".
								"WHERE q.formulaire_question = 'DEVIS' ".
								"ORDER BY q.rang_question"
				);
				
		$out = array();
		
		foreach ($result as $r) {
			$key = $r['q']['id_question'];
			// si cette question n'a pas déjà été croisée, on l'ajout a la liste des question
			if(!isset($out[$key])) {
				$out[$key] = Array();
				$out[$key]['question'] = $r['q'];
			}
			// on ajoute la réponse à la liste
			if($r['r']['id_reponse'] != null) {
				$out[$key]['reponses'][$r['r']['id_reponse']] = $r['r'];
			}
		}
		return $out;
	}
	
	
	// renvoie toutes les questions et leurs réponses. Attention, seulement les questions actives !
	// paramètres :
	// - restriction_type : optionelle, peut être un tableau contenant les types admis (ENTIER/ARTICLE/TEXTE/LISTE/OUINON)
	public function toutesLesQuestionsEtLeursReponses($restriction_type = null) {
		// on selectionne l'ensemble des questions
		$where = '';
		if($restriction_type != null) {
			$first = true;
			$where .= ' AND (';
			foreach ($restriction_type as $type) {
				if($first) $first = false; else $where .= ' OR ';
				$where .= "q.type_reponse = '$type'";
			}
			$where .= ')';
		}
		
		$result = $this->query("SELECT * ".
								"FROM questions q ".
								"LEFT JOIN reponses r ON q.id_question = r.id_question ".
								"LEFT JOIN nouveau_statuts ns ON ns.id_reponse = r.id_reponse ". 
								"LEFT JOIN bloc_questions bq ON bq.id_bloc = q.id_bloc ".
								"LEFT JOIN vox_blocs vb ON vb.id_vox_bloc = bq.id_vox_bloc ".
								"WHERE q.active AND q.type_reponse <> 'TEXTE'$where ".
								"ORDER BY vb.rang_vox_bloc, bq.rang_bloc, q.rang_question"
				);
		$out = array();
		
		foreach ($result as $r) {
			$key = $r['vb']['rang_vox_bloc'].'_'.$r['bq']['rang_bloc'].'_'.$r['q']['rang_question'];
			// si cette question n'a pas d�j� �t� crois�, on l'ajout a la liste des question
			if(!isset($out[$key])) {
				$out[$key] = Array();
				$out[$key]['info'] = $r['q'];
			}
			// on ajoute la r�ponse � la liste
			if($r['r']['id_reponse'] != null) {
				$out[$key]['reponses'][$r['r']['id_reponse']] = $r['r'];
			}
		}
		return $out;
	}
	
	// renvoi un tableau avec la liste des questions qui sont influencées par plus d'une règle
	public function questionsReglesConflits() {
		$result = $this->query("select Q.id_question, Q.libelle_question, AQ.rang_question, QI.libelle_question, R.*, A.description_article, AQ.* ".
								"from questions Q ".
								"join (select id_question from action_questions group by id_question having count(*)>1) D ".
								"on D.id_question=Q.id_question ".
								"join action_questions AQ on AQ.id_question=Q.id_question ".
								"join reponses R on R.id_reponse=AQ.id_reponse ".
								"join questions QI on QI.id_question=R.id_question ".
								"left join articles A on A.id_article=R.id_article ".
								"order by Q.id_question, AQ.rang_question");
								
		return $result;
	}
	
	// supprime une question de type 'devis', ses reponses et ses regles
	// $id_question : l'id de la question a supprimer
	public function supprimerQuestionDevis($id_question) {
		// on cherche les regles (tarifs calcules) que la question engendre
		$regle = $this->query("SELECT q.id_question as question, t.id_tarif as tarif ".
							"FROM questions q ".
							"JOIN reponses r ON r.id_question=q.id_question ".
							"JOIN tarifs t ON t.id_reponse=r.id_reponse ".
							"JOIN tarif_calcules tc ON tc.id_tarif=t.id_tarif ".
							"WHERE q.id_question=".$id_question);
		
		// au moins une regle, on supprime
		if(isset($regle[0]['t']['tarif'])) {
			$this->query("DELETE FROM tarif_calcules WHERE id_tarif=".$regle[0]['t']['tarif']);
			$this->query("DELETE FROM tarifs WHERE id_tarif=".$regle[0]['t']['tarif']);
		}
		$this->query("DELETE FROM reponses WHERE id_question=".$id_question);
		$this->query("DELETE FROM questions WHERE id_question=".$id_question);
		
		$this->layout = '';
	}
	
/**
 * Primary key field
 *
 * @var string
 */
	public $primaryKey = 'id_question';
/**
 * Display field
 *
 * @var string
 */
	public $displayField = 'libelle_question';
/**
 * Validation rules
 *
 * @var array
 */
	public $validate = array(
		'id_question' => array(
			'numeric' => array(
				'rule' => array('numeric'),
				//'message' => 'Your custom message here',
				//'allowEmpty' => false,
				//'required' => false,
				//'last' => false, // Stop validation after this rule
				//'on' => 'create', // Limit validation to 'create' or 'update' operations
			),
		),
		'question_sur_societe' => array(
			'boolean' => array(
				'rule' => array('boolean'),
				//'message' => 'Your custom message here',
				//'allowEmpty' => false,
				//'required' => false,
				//'last' => false, // Stop validation after this rule
				//'on' => 'create', // Limit validation to 'create' or 'update' operations
			),
		),
		'rang_question' => array(
			'numeric' => array(
				'rule' => array('numeric'),
				//'message' => 'Your custom message here',
				//'allowEmpty' => false,
				//'required' => false,
				//'last' => false, // Stop validation after this rule
				//'on' => 'create', // Limit validation to 'create' or 'update' operations
			),
		),
		'id_bloc' => array(
			'notempty' => array(
				'rule' => array('notempty'),
				//'message' => 'Your custom message here',
				//'allowEmpty' => false,
				//'required' => false,
				//'last' => false, // Stop validation after this rule
				//'on' => 'create', // Limit validation to 'create' or 'update' operations
			),
		),
		'id_categorie' => array(
			'numeric' => array(
				'rule' => array('numeric'),
				//'message' => 'Your custom message here',
				//'allowEmpty' => false,
				//'required' => false,
				//'last' => false, // Stop validation after this rule
				//'on' => 'create', // Limit validation to 'create' or 'update' operations
			),
			'notempty' => array(
				'rule' => array('notempty'),
				//'message' => 'Your custom message here',
				//'allowEmpty' => false,
				//'required' => false,
				//'last' => false, // Stop validation after this rule
				//'on' => 'create', // Limit validation to 'create' or 'update' operations
			),
		),
	);

	//The Associations below have been created with all possible keys, those that are not needed can be removed

/**
 * hasOne associations
 *
 * @var array
 */
	public $hasOne = array(
		'bloc_question' => array(
			'className' => 'bloc_question',
			'foreignKey' => 'id_bloc',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),
		'articles_categorie' => array(
			'className' => 'articles_categorie',
			'foreignKey' => 'id_categorie',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		)
	);

/**
 * hasMany associations
 *
 * @var array
 */
	public $hasMany = array(
		'reponses_donnee' => array(
			'className' => 'reponses_donnee',
			'foreignKey' => 'id_question',
			'dependent' => false,
			'conditions' => '',
			'fields' => '',
			'order' => '',
			'limit' => '',
			'offset' => '',
			'exclusive' => '',
			'finderQuery' => '',
			'counterQuery' => ''
		),
		'action_question' => array(
			'className' => 'action_question',
			'foreignKey' => 'id_question',
			'dependent' => false,
			'conditions' => '',
			'fields' => '',
			'order' => '',
			'limit' => '',
			'offset' => '',
			'exclusive' => '',
			'finderQuery' => '',
			'counterQuery' => ''
		),
		'reponse' => array(
			'className' => 'reponse',
			'foreignKey' => 'id_question',
			'dependent' => false,
			'conditions' => '',
			'fields' => '',
			'order' => '',
			'limit' => '',
			'offset' => '',
			'exclusive' => '',
			'finderQuery' => '',
			'counterQuery' => ''
		)
	);

}
